package demo.repository;

import demo.entity.PM;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 私信 Dao
 *
 * @author 肖溥博文
 * @version 1.0
 */
@Repository
public interface PMRepository extends JpaRepository<PM, Long> {

    /**
     * 按 发送人 uid 和 接收人 uid 查找私信
     *
     * @param senderUid    发送人 uid
     * @param recipientUid 接收人 uid
     * @return 私信对象的数组
     */
    List<PM> findAllBySenderUidAndRecipientUid(Long senderUid, Long recipientUid);

    /**
     * 按 私信 id 查找私信对象
     *
     * @param id 私信 id
     * @return 私信对象
     */
    PM findPMById(Long id);

    /**
     * 按双方 uid 查询所有私信记录
     *
     * @param uid1 用户 1 的 uid
     * @param uid2 用户 2 的 uid
     * @return 私信对象的数组
     */
    @Query(nativeQuery = true, value = " select * from pm where (sender_uid = :uid1 and recipient_uid = :uid2) or (sender_uid = :uid2 and recipient_uid = :uid1) order by sending_time")
    List<PM> fetchPMsByUids(Long uid1, Long uid2);

//    @Query(nativeQuery = true, value = "select sender_uid from pm where recipient_uid = :uid union select recipient_uid from PM where sender_uid = :uid")
//    List<Long> findAllBySenderUidOrRecipientUid(Long uid);

    /**
     * 查询消息列表所有的联系人
     *
     * @param uid 用户 id
     * @return 联系人 uid 的数组
     */
    @Query(nativeQuery = true, value = "select distinct uid from (select sender_uid as uid, sending_time from pm where recipient_uid = :uid union select recipient_uid as uid,sending_time from pm where sender_uid = :uid order by sending_time desc) as temp")
    List<Long> fetchLatestContactsByUid(@Param("uid") Long uid);
//
//    /**
//     * 查询消息列表所有的最新消息发送时间
//     *
//     * @param uid
//     * @return
//     */
//    @Query(nativeQuery = true, value = "select max(sending_time) from (select sender_uid as uid,sending_time from pm where recipient_uid = :uid union select recipient_uid as uid,sending_time from pm where sender_uid = :uid order by sending_time desc) as temp group by uid")
//    List<Date> fetchLatestSendingTimesByUid(@Param("uid") Long uid);

    /**
     * 查询消息列表的所有消息编号
     *
     * @param uid 用户 id
     * @return 消息 id 的数组
     */
    @Query(nativeQuery = true, value = "select max(id) from (select sender_uid as uid,sending_time,id from pm where recipient_uid = :uid union select recipient_uid as uid,sending_time,id from pm where sender_uid = :uid order by sending_time desc) as temp group by uid")
    List<Long> fetchLatestPMIdByUid(@Param("uid") Long uid);


//    /**
//     * 查询消息列表的所有最新消息的内容
//     *
//     * @param uid
//     * @return
//     */
//    @Query(nativeQuery = true, value = "select content from (select content, max(id) from ((select sender_uid as uid,sending_time,content,id from pm where recipient_uid = :uid union select recipient_uid as uid,sending_time,content,id from pm where sender_uid = :uid order by sending_time desc) as temp group by uid)) as newtemp")
//    List<String> fetchLatestContentsByUid(@Param("uid") Long uid);
}
